home *** CD-ROM | disk | FTP | other *** search
/ PC Answers 1995 May / PC Answers CD-ROM 7 (Future Publishing) (May 1995).iso / vbits / code / pleas / ole / excel / amort.txt next >
Encoding:
Text File  |  1994-06-10  |  789 b   |  28 lines
  1. ' 1. opens AMORTIZE.XLS
  2. ' 2. plugs in values for the variables
  3. ' 3. and pops the calculated payment up in a message box
  4. '     C7    =Loan_amount
  5. '     C8    =Annual_interest_rate
  6. '     C9    =Term_in_years
  7. '     C10    =Payments_per_year
  8. '     C14    =Calculated_payment
  9.  
  10. Dim APPXL As object
  11. Dim XL As object
  12. Dim ws As object
  13.  
  14. Set APPXL = GetObject(, "Excel.Application")
  15. Set XL = APPXL.Application
  16. XL.WorkBooks.Open "F:\TEMP\AMORTIZE.XLS"
  17. Set ws = XL.ActiveSheet
  18. ws.Range("Loan_amount").Value = 100000
  19. ws.Range("Annual_interest_rate").Value = .075
  20. ws.Range("Term_in_years").Value = 30
  21. ws.Range("Payments_per_year").Value = 12
  22. MsgBox Format$(ws.Range("Calculated_payment").Value, "currency"), , "Payment"
  23. XL.Workbooks(1).[Close] (False)
  24.  
  25. Set ws = Nothing
  26. Set XL = Nothing
  27. Set APPXL = Nothing
  28.